﻿DROP  VIEW [dbo].[vi2513]
GO
CREATE VIEW [dbo].[vi2513] AS 
Select 
	-- C¸ nh©n
	HT00.DivisionID, HT00.EmployeeID, 
	HT00.Orders,HT00.S1,HT00.S2,HT00.S3,
	Ltrim(RTrim(isnull(HT00.LastName,'')))+ ' ' + LTrim(RTrim(isnull(HT00.MiddleName,''))) + ' ' + LTrim(RTrim(Isnull(HT00.FirstName,''))) As FullName,
	HT00.LastName, HT00.MiddleName, HT00.FirstName,
	HT00.ShortName, HT00.Alias, HT00.Birthday, HT00.BornPlace, 

	(Case When HT00.IsMale=1 then 'Nam' else 'Nữ' End) as IsMale, 
	
	HT00.NativeCountry, HT00.PassportNo, HT00.PassportDate, 
	HT00.PassportEnd, HT00.IdentifyCardNo, HT00.IdentifyDate, HT00.IdentifyPlace, 

	(Case When HT00.IsSingle=1 then 'Độc thân' else 'Đã lập gia đình' End) as IsSingle, 
	(Case HT00.EmployeeStatus
		when 0 then 'Tuyeån duïng'
		when 1 then 'Ñang laøm'
		when 2 then 'Thoâi vieäc'
		when 3 then 'Taïm nghæ'
		else 'Nghæ vieäc' end) as Status,
	
	HT00.ImageID, HT00.CountryID, 
	LTrim(RTrim(Isnull(HT00.PermanentAddress,''))) + ' ' + LTrim(RTrim(Isnull(HT00.DistrictID,''))) + ' ' +
	(Case when isnull(HT00.CityID,'') <> '' then  LTrim(RTrim(isnull(AT02.CityName,'')))  else  ''  End  ) As FullAddress,
	--LTrim(RTrim(isnull(HT00.CityID,'')))  As FullAddress, 
	HT00.CityID, AT02.CityName, HT00.DistrictID, HT00.PermanentAddress, HT00.TemporaryAddress,

	HT00.EthnicID, tb2509.EthnicName, HT00.ReligionID, tb2510.ReligionName, 

	HT00.Notes, HT00.HealthStatus, HT00.HomePhone, 
	HT00.HomeFax, HT00.MobiPhone, HT00.Email,
	HT00.CreateDate, HT00.CreateUserID, HT00.LastModifyDate, HT00.LastModifyUserID, HT03.IsOtherDayPerMonth,
	HT00.IsMale as IsMaleID, HT00.IsSingle as IsSingleID,HT00.IsForeigner,HT00.RecruitTimeID,


	-- Gia ®×nh
	HT01.FatherName, HT01.FatherYear, HT01.FatherJob, HT01.FatherAddress, HT01.FatherNote,
	HT01.IsFatherDeath, 
	HT01.MotherName, HT01.MotherYear, HT01.MotherJob,HT01.MotherAddress, HT01.MotherNote,
	HT01.IsMotherDeath,
	HT01.SpouseName, HT01.SpouseYear, HT01.SpouseAddress, HT01.SpouseNote,
	HT01.SpouseJob, HT01.IsSpouseDeath, 
	HT01.EducationLevelID, HT05.EducationLevelName, 
	HT01.PoliticsID, 
	HT01.Language1ID, HT01.Language2ID, HT01.Language3ID, HT01.LanguageLevel1ID,
	
	HT01.LanguageLevel2ID, HT01.LanguageLevel3ID, --HT07.LanguageLevelName as LanguageLevelName1,

	-- Th«ng tin x· héi
	HT02.BankID, HT02.BankAccountNo, HT02.SoInsuranceNo, HT02.SoInsurBeginDate,
	HT02.HeInsuranceNo, HT02.ArmyJoinDate, HT02.ArmyEndDate, HT02.ArmyLevel,
	HT02.Hobby, HT02.HospitalID, HT02.Height, HT02.Weight, HT02.BloodGroup,T05.AssociationID,

	-- Thong tin ve he so chi tieu
	HT03.SalaryCoefficient, HT03.DutyCoefficient, HT03.TimeCoefficient, HT00.DepartmentID, A03.DepartmentName,
	ISNULL(HT00.TeamID,'') AS TEAMID,T01.TeamName, HT03.DutyID, DutyName, HT03.TaxObjectID, HT00.EmployeeStatus, HT03.Experience,
	HT03.SuggestSalary, HT03.RecruitDate, HT03.RecruitPlace, HT03.WorkDate,HT03.LeaveDate, HT07.QuitJobID, HT07.QuitJobName,
	HT03.C01, HT03.C02, HT03.C03, HT03.C04, HT03.C05, HT03.C06, HT03.C07,
	HT03.C08, HT03.C09, HT03.C10, HT03.C11, HT03.C12, HT03.C13, HT03.BaseSalary, HT03.InsuranceSalary,
	HT03.Salary01, HT03.Salary02, HT03.Salary03, HT03.Target01ID, HT03.Target02ID,
	HT03.Target03ID, HT03.Target04ID, HT03.Target05ID, HT03.Target06ID, HT03.Target07ID,
	HT03.Target08ID, HT03.Target09ID, HT03.Target10ID, HT03.TargetAmount01, 
	HT03.TargetAmount02, HT03.TargetAmount03, HT03.TargetAmount04, HT03.TargetAmount05,
	HT03.TargetAmount06, HT03.TargetAmount07, HT03.TargetAmount08, HT03.TargetAmount09, HT03.TargetAmount10, HT03.LoaCondID,
	HT03.ApplyDate, HT03.BeginProbationDate, HT03.EndProbationDate, HT03.ProbationNote, HT03. FileID


From tb2554 As HT00 
		Left Join tb2555 As HT01 On HT00.EmployeeID = HT01.EmployeeID and  HT00.DivisionID = HT01.DivisionID
		Left Join tb2556 As HT02 On HT00.EmployeeID = HT02.EmployeeID and  HT00.DivisionID = HT02.DivisionID
		Left Join tb2557 As HT03 On HT00.EmployeeID = HT03.EmployeeID and  HT00.DivisionID = HT03.DivisionID
		Left Join tb2559 As T05 On HT00.EmployeeID = T05.EmployeeID
		Left Join tb2535 As T01 On HT00.TeamID = T01.TeamID and
					HT00.DepartmentID =T01.DepartmentID
		Left Join tb2541 HT07 On HT03.QuitJobID = HT07.QuitJobID
		Left Join tb2509  On HT00.EthnicID = tb2509.EthnicID
		Left Join tb2510  On HT00.ReligionID = tb2510.ReligionID
		Left Join tb2536 on HT03.DutyID = tb2536.DutyID
		Left join tb2513 HT05 on HT05.EducationLevelID = HT01.EducationLevelID
--		Left join tb2515 HT07 on HT07.LanguageLevelID = HT01.LanguageLevel1ID
		Left join tb0022 AT02 on isnull(HT00.CityID,'') = isnull(AT02.CityID,'')
		Left join tb0039 A03 on A03.DivisionID = HT00.DivisionID and A03.DepartmentID = HT00.DepartmentID

GO